package com.ruoyi.project.excelmerge.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ruoyi.common.core.domain.model.LoginUser;
import com.ruoyi.common.helper.LoginHelper;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.core.domain.PageQuery;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ruoyi.project.excelmerge.domain.Apparatus;
import com.ruoyi.project.excelmerge.domain.ApparatusExcel;
import com.ruoyi.project.excelmerge.utils.ApparatusListener;
import com.ruoyi.project.fujian.domain.vo.AFujianVo;
import com.ruoyi.project.fujian.service.IAFujianService;
import com.ruoyi.project.fujian.service.impl.AFujianServiceImpl;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import org.flowable.spring.boot.app.App;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import com.ruoyi.project.excelmerge.domain.bo.AExcelmergeBo;
import com.ruoyi.project.excelmerge.domain.vo.AExcelmergeVo;
import com.ruoyi.project.excelmerge.domain.AExcelmerge;
import com.ruoyi.project.excelmerge.mapper.AExcelmergeMapper;
import com.ruoyi.project.excelmerge.service.IAExcelmergeService;

import java.io.File;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * excel合并Service业务层处理
 *
 * @author ruoyi
 * @date 2024-03-02
 */
@RequiredArgsConstructor
@Service
public class AExcelmergeServiceImpl implements IAExcelmergeService {

    private final AExcelmergeMapper baseMapper;

    @Autowired
    public IAFujianService fujianService;

    @Value("${upload.path}")
    private String uploadPath;


    /**
     * 查询excel合并
     */
    @Override
    public AExcelmergeVo queryById(String id){
        return baseMapper.selectVoById(id);
    }

    /**
     * 查询excel合并列表
     */
    @Override
    public TableDataInfo<AExcelmergeVo> queryPageList(AExcelmergeBo bo, PageQuery pageQuery) {
        LambdaQueryWrapper<AExcelmerge> lqw = buildQueryWrapper(bo);
        Page<AExcelmergeVo> result = baseMapper.selectVoPage(pageQuery.build(), lqw);
        return TableDataInfo.build(result);
    }

    /**
     * 查询excel合并列表
     */
    @Override
    public List<AExcelmergeVo> queryList(AExcelmergeBo bo) {
        LambdaQueryWrapper<AExcelmerge> lqw = buildQueryWrapper(bo);
        return baseMapper.selectVoList(lqw);
    }

    private LambdaQueryWrapper<AExcelmerge> buildQueryWrapper(AExcelmergeBo bo) {
        Map<String, Object> params = bo.getParams();
        LambdaQueryWrapper<AExcelmerge> lqw = Wrappers.lambdaQuery();
        lqw.eq(StringUtils.isNotBlank(bo.getTitle()), AExcelmerge::getTitle, bo.getTitle());
        lqw.eq(StringUtils.isNotBlank(bo.getFjidone()), AExcelmerge::getFjidone, bo.getFjidone());
        lqw.eq(StringUtils.isNotBlank(bo.getFjidtwo()), AExcelmerge::getFjidtwo, bo.getFjidtwo());
        lqw.eq(StringUtils.isNotBlank(bo.getFjidthree()), AExcelmerge::getFjidthree, bo.getFjidthree());
        lqw.eq(StringUtils.isNotBlank(bo.getFjidfour()), AExcelmerge::getFjidfour, bo.getFjidfour());
        lqw.eq(StringUtils.isNotBlank(bo.getCreateuserid()), AExcelmerge::getCreateuserid, bo.getCreateuserid());
        lqw.like(StringUtils.isNotBlank(bo.getCreateusername()), AExcelmerge::getCreateusername, bo.getCreateusername());
        lqw.eq(StringUtils.isNotBlank(bo.getCreatedeptid()), AExcelmerge::getCreatedeptid, bo.getCreatedeptid());
        lqw.like(StringUtils.isNotBlank(bo.getCreatedeptname()), AExcelmerge::getCreatedeptname, bo.getCreatedeptname());
        lqw.eq(bo.getCreatetime() != null, AExcelmerge::getCreatetime, bo.getCreatetime());
        return lqw;
    }

    /**
     * 新增excel合并
     */
    @Override
    public AExcelmerge insertByBo(AExcelmergeBo bo) {
        AExcelmerge add = BeanUtil.toBean(bo, AExcelmerge.class);
        LoginUser loginUser = LoginHelper.getLoginUser();
        add.setId(IdUtil.simpleUUID());  // id
        add.setCreateuserid(loginUser.getUserId().toString());
        add.setCreateusername(loginUser.getUsername());
        add.setCreatedeptid(loginUser.getDeptId().toString());
        add.setCreatedeptname(loginUser.getDeptName());
        add.setCreatetime(new Date());
        validEntityBeforeSave(add);
        baseMapper.insert(add);
        return add;
    }

    /**
     * 修改excel合并
     */
    @Override
    public AExcelmerge updateByBo(AExcelmergeBo bo) {
        AExcelmerge update = BeanUtil.toBean(bo, AExcelmerge.class);
        validEntityBeforeSave(update);
        baseMapper.updateById(update);
        return update;
    }

    /**
     * 保存前的数据校验
     */
    private void validEntityBeforeSave(AExcelmerge entity){
        //TODO 做一些数据校验,如唯一约束
    }

    /**
     * 批量删除excel合并
     */
    @Override
    public Boolean deleteWithValidByIds(Collection<String> ids, Boolean isValid) {
        if(isValid){
            //TODO 做一些业务上的校验,判断是否需要校验
        }
        return baseMapper.deleteBatchIds(ids) > 0;
    }

    @Override
    public JSONObject genExcel(String id) {
        JSONObject result = new JSONObject();
        // 1.通过id查询出详细数据
        AExcelmergeVo vo = baseMapper.selectVoById(id);
        // 2. 获取去年，今年的附件, 查询附件想详细信息，并获取File
        AFujianVo fj1 = fujianService.queryById(vo.getFjidone());
        AFujianVo fj2 = fujianService.queryById(vo.getFjidtwo());
        // 构建新的文件名和目标路径
        File file1 = new File(uploadPath + fj1.getFjpath());
        File file2 = new File(uploadPath + fj2.getFjpath());
        // 3.判断文件是否存在
        if (!file1.exists() || !file2.exists()) {
            result.put("msg", "文件不存在");
            return result;
        }
        // 3.1 获取文件后缀，必须是excel
        if((!".xls".equals(fj1.getFjtype()) && !".xlsx".equals(fj1.getFjtype())) || (!".xls".equals(fj2.getFjtype()) && !".xlsx".equals(fj2.getFjtype()))) {
            result.put("msg", "请上传Excel附件");
            return result;
        }
        // 4.开始生成Excel
        generateExcels(file1, file2);
//        generateExcels2(file1, file2);

        return null;
    }

    private void generateExcels2(File file1, File file2){
        // 获取去年数据
        List<Apparatus> data1 = getApparatusData(file1);
        // 获取今年数据
        List<Apparatus> data2 = getApparatusData(file2);
        // 分组获取去年的数据
        Map<String, Map<String, Double>> groupedItems = data1.stream()
            .collect(Collectors.groupingBy(Apparatus::getName,
                Collectors.groupingBy(Apparatus::getType,
                    Collectors.summingDouble(item -> item.getCount() * item.getPrice()))));
        // 计算单价并赋值
        data1.forEach(item -> {
            double totalPrice = groupedItems.get(item.getName()).get(item.getType());
            int totalQuantity = data1.stream()
                .filter(i -> i.getName().equals(item.getName()) && i.getType().equals(item.getType()))
                .mapToInt(Apparatus::getCount)
                .sum();
            double unitPrice = totalPrice / totalQuantity;
            item.setPrice(unitPrice);
        });
        // 分组获取今年数据
        Map<String, Map<String, Double>> groupedItems2 = data2.stream()
            .collect(Collectors.groupingBy(Apparatus::getName,
                Collectors.groupingBy(Apparatus::getType,
                    Collectors.summingDouble(item -> item.getCount() * item.getPrice()))));
        // 计算单价并赋值
        data2.forEach(item -> {
            double totalPrice = groupedItems2.get(item.getName()).get(item.getType());
            int totalQuantity = data2.stream()
                .filter(i -> i.getName().equals(item.getName()) && i.getType().equals(item.getType()))
                .mapToInt(Apparatus::getCount)
                .sum();
            double unitPrice = totalPrice / totalQuantity;
            item.setPrice(unitPrice);
        });

        // 输出结果
        data1.forEach(System.out::println);
    }

    // 校验两个数据中的
    private void generateExcels(File file1, File file2) {
        // 获取去年数据
        List<Apparatus> data1 = getApparatusData(file1);
        // 获取今年数据
        List<Apparatus> data2 = getApparatusData(file2);
        // 今年新增数据
        List<Apparatus> resultAdd = new ArrayList<>();
        // 今年减少的数据
        List<Apparatus> resultReduce = new ArrayList();
        // 获取去年分组数据
        Map<String, List<Apparatus>> qnGroupData = getGroupData(data1);
        // 获取今年分组数据
        Map<String, List<Apparatus>> jnGroupData = getGroupData(data2);

        /**
         * 将分组后的数据进行比较
         */
        jnGroupData.forEach((key, value) -> {
            qnGroupData.forEach((key2, value2) -> {
                // 1.先判断当前的key是否相同
                if(key.contains(key2) || key2.contains(key)) {
                    // 计算出今年的数据以及去年的数据
                    String name = "";
                    String type = "";
                    Integer count = 0;
                    Double price = 0.0;
                    for (Apparatus apparatus : value) {
                        // 遍历之前先判断当前的数据是否命中
                        if(apparatus.getFill() == null || !apparatus.getFill()) {
                            name = apparatus.getName();
                            type = apparatus.getType();
                            count += apparatus.getCount();
                            price = apparatus.getPrice();
                            apparatus.setFill(true);
                        }
                    }
                    // 计算出去年的数据以及去年的数据
                    Integer count2 = 0;
                    Double price2 = 0.0;
                    String name2 = "";
                    String type2 = "";
                    for (Apparatus apparatus : value2) {
                        if(apparatus.getFill() == null || !apparatus.getFill()) {
                            count2 += apparatus.getCount();
                            price2 = apparatus.getPrice();
                            name2 = apparatus.getName();
                            type2 = apparatus.getType();
                            apparatus.setFill(true);
                        }
                    }
                    // 判断今年比去年新增了多少
                    if (count > count2) {  // 多
                        Apparatus apparatus = new Apparatus();
                        int row = count - count2;
                        apparatus.setCount(row);
                        apparatus.setName(name);
                        apparatus.setPrice(price);
                        apparatus.setTotalPrice(row * price);
                        apparatus.setType(type);
                        resultAdd.add(apparatus);
                    }else if (count < count2) { // 少
                        int row = count2 - count;
                        Apparatus apparatus = new Apparatus();
                        apparatus.setCount(row);
                        apparatus.setName(name2);
                        apparatus.setPrice(price2);
                        apparatus.setTotalPrice(row * price2);
                        apparatus.setType(type2);
                        resultReduce.add(apparatus);
                    }
                }
            });
        });

        /**
         * 遍历数据获取没有处理过的数据，这些数据都是新增数据
         */
        // 过滤出 fill 字段为 true 的数据
        Map<String, List<Apparatus>> filteredDataMap = jnGroupData.entrySet().stream()
            .collect(Collectors.toMap(Map.Entry::getKey, entry ->
                ((List<Apparatus>) entry.getValue()).stream()
                    .filter(obj -> obj != null && (((Apparatus) obj).getFill()==null || !((Apparatus) obj).getFill()))
                    .collect(Collectors.toList())
            ));

        /**
         * 遍历数据，将数据添加到今年新增里边
         */
        filteredDataMap.forEach((key, value) -> {
            resultAdd.addAll(value);
        });


        String fileName = "D:\\ruoyi\\upload\\example.xlsx"; // 输出的 Excel 文件名
        EasyExcel.write(fileName)
            .head(ApparatusExcel.class) // 指定 Excel 表头的类
            .sheet("Sheet1") // 指定 Sheet 名称
            .doWrite(resultAdd); // 写入数据

    }
    // 通过Excel读取数据
    private List<Apparatus> getApparatusData(File file1) {
        List<Apparatus> apparatusArrayList = new ArrayList<Apparatus>();
        EasyExcel.read(file1, Apparatus.class, new AnalysisEventListener<Apparatus>() {
            @Override
            public void invoke(Apparatus apparatus, AnalysisContext analysisContext) {
                apparatusArrayList.add(apparatus);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {

            }
        }).sheet().doRead();
        return apparatusArrayList;
    }

    // 将数据进行分钟
    private Map<String, List<Apparatus>> getGroupData(List<Apparatus> data) {
        Map<String, List<Apparatus>> qnfzList = new HashMap<>();
        for (Apparatus apparatus : data) {
            String twoName = (apparatus.getName().trim() + apparatus.getType().trim()).replaceAll("\\(", "").replaceAll("\\)", "");
            // 存在则添加
            if (qnfzList.containsKey(twoName)) {
                qnfzList.get(twoName).add(apparatus);
            }else {
                List<Apparatus> group = new ArrayList<>();
                group.add(apparatus);
                qnfzList.put(twoName, group);
            }
        }
        return qnfzList;
    }

}
